
import pandas as pd
import numpy as np
import math
import re
import datetime as dt
from IPython.display import HTML
import warnings
warnings.filterwarnings('ignore')


dogs = pd.read_csv("/Users/darrews/Desktop/adoptions/dogs.csv")
dogtravel = pd.read_csv("/Users/darrews/Desktop/adoptions/dogTravel.csv")
pop = pd.read_csv("/Users/darrews/Desktop/adoptions/NST-EST2021-POP.csv" , header = None)


# FIX CSV

# Questa funzione controlla se ci sono numeri in una stringa

def has_numbers(inputString): 
    return bool(re.search(r'\d', inputString))
id_problematici = []
k = 0

# Se ci sono lettere nella variabile posted (dovrebbe essere una data) 
#l'id viene aggiunto in una lista

for i in dogs["posted"]:
    if has_numbers(dogs["posted"][k]) == True: 
        pass
    else:
        id_problematici.append([dogs["id"][k]])
    k+=1
    
# Shift di variabili per le righe con problemi così che 
# in "posted" ci sia una data e non una città
    
for i in range(len(dogs["id"])):
  if dogs["id"][i] in id_problematici:
    dogs.iloc[i:i+1,25:34] = dogs.iloc[i:i+1,25:34].shift(periods = 1, axis = 1)
  else:
    pass


## 1) Extract all dogs whose status is different from adoptable

dogs[dogs["status"] != "adoptable"]


## 2) For each primary breed, determine the number of dogs

dogs.groupby("breed_primary").count()["id"]

breed_primary
Affenpinscher                         17
Afghan Hound                           4
Airedale Terrier                      19
Akbash                                 3
Akita                                181
                                    ... 
Wirehaired Pointing Griffon            1
Wirehaired Terrier                    60
Xoloitzcuintli / Mexican Hairless     11
Yellow Labrador Retriever            158
Yorkshire Terrier                    360
Name: id, Length: 216, dtype: int64


## 3) For each (primary) breed, determine the ratio between 
#the number of dogs of Mixed Breed and those not of Mixed Breed.

# Per ogni primary_breed viene effettuato un rapporto tra i cani
#che hanno "Mixed Breed" come secondary_breed e coloro che non presentano 
#tale valore

dogs[dogs['breed_secondary'] == 'Mixed Breed'].groupby("breed_primary").count()["id"]/dogs[dogs['breed_secondary'] != 'Mixed Breed'].groupby("breed_primary").count()["id"]

# NaN Values are determined by the absence of secondary mixed breed.

breed_primary
Affenpinscher                        0.062500
Afghan Hound                              NaN
Airedale Terrier                     0.055556
Akbash                                    NaN
Akita                                0.034286
                                       ...   
Wirehaired Pointing Griffon               NaN
Wirehaired Terrier                   0.071429
Xoloitzcuintli / Mexican Hairless         NaN
Yellow Labrador Retriever                 NaN
Yorkshire Terrier                    0.043478
Name: id, Length: 216, dtype: float64


## 4) For each (primary) breed, determine the earliest and the latest 
# posted timestamp.

# Trasformare posted in datetime tramite la funzione

dogs["posted"] = pd.to_datetime(dogs["posted"])


# raggruppare per razza

group = dogs.groupby(['breed_primary'])

# Trovare i timestamp postati per primi e per ultimi

result = group.agg({ 'posted' : [np.min, np.max]})
result.columns = result.columns.get_level_values(0)
result.columns = ["Earliest","Latest"]
result


# 5) For each state, compute the sex imbalance, that is the difference 
#between male and female dogs. 
#    In which state this imbalance is largest?

# Per ogni stato veiene calcolata la differenza tra il numero di cani 
#con variabile "sex" uguale a "Male" e il numero di cani 
#con variabile "sex" uguale a "Female"

imb = pd.DataFrame(dogs[dogs['sex'] == 'Male'].groupby("contact_state").count()["id"] - dogs[dogs['sex'] == 'Female'].groupby("contact_state").count()["id"])
imb.rename(columns = {'id':'imbalance'}, inplace = True)
imb


print("The state with the maximum imbalance is:", str(imb.index[imb['imbalance']== imb["imbalance"].max()][0]))

The state with the maximum imbalance is: OH


# 6) For each pair (age, size), determine the average duration of the 
# stay and the average cost of stay.

# Viene effettuato un doppio group by sulle variabili "age" e "size" 
#alle quali vengono affiancati i valori medi per "stay_duration"
#e "stay_cost" relativi ad ogni categoria delle due colonne raggruppate.

dogs.groupby(["age","size"]).mean()[["stay_duration","stay_cost"]]


# 7) Find the dogs involved in at least 3 travels. 
#Also list the breed of those dogs. 

#id dei due database sono trasformati in stringhe
dogtravel["id"].apply(str)
dogs["id"].apply(str)

# gli id vengono impostati come indici  
dogtravel.set_index('id')
dogs.set_index("id")

# tabella dei cani che hanno effettuato almeno 3 viaggi
tab1 = dogtravel.groupby("id").count()[dogtravel.groupby("id").count()["index"]>=3]["index"]

# join tra la tabella appena creata e il database dei cani per 
#trovare la razza 
result9 = pd.merge(tab1, dogs[["id","breed_primary"]], left_on = "id", right_on = "id")
result9 = result9.rename(columns = {"index":"Travels"})
result9


# 8) Fix the travels table so that the correct state is computed from the 
#manual and the found fields.
#    If manual is not missing, then it overrides what is stored in found.

# Prima si fa un controllo su "manual"
# Se manual non è missing si va a sostituire il valore in "found" 
# con quello di "manual"

dogtravel8 = dogtravel
for i in range(len(dogtravel["manual"])):
    if pd.isnull(dogtravel["manual"][i]) == True:
        pass
    else:
        dogtravel8["found"][i] = dogtravel["manual"][i]
dogtravel8


# 9) For each state, compute the ratio between the number of travels and 
#the population.

# Prima si importa un dizionario contenente tutte le sigle degli stati

states = [ 'AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA',
           'HI', 'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA', 'MA', 'MD', 'ME',
           'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH', 'NJ', 'NM',
           'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX',
           'UT', 'VA', 'VT', 'WA', 'WI', 'WV', 'WY']

states = {
    'AK': 'Alaska',
    'AL': 'Alabama',
    'AR': 'Arkansas',
    'AZ': 'Arizona',
    'CA': 'California',
    'CO': 'Colorado',
    'CT': 'Connecticut',
    'DC': 'District of Columbia',
    'DE': 'Delaware',
    'FL': 'Florida',
    'GA': 'Georgia',
    'HI': 'Hawaii',
    'IA': 'Iowa',
    'ID': 'Idaho',
    'IL': 'Illinois',
    'IN': 'Indiana',
    'KS': 'Kansas',
    'KY': 'Kentucky',
    'LA': 'Louisiana',
    'MA': 'Massachusetts',
    'MD': 'Maryland',
    'ME': 'Maine',
    'MI': 'Michigan',
    'MN': 'Minnesota',
    'MO': 'Missouri',
    'MS': 'Mississippi',
    'MT': 'Montana',
    'NC': 'North Carolina',
    'ND': 'North Dakota',
    'NE': 'Nebraska',
    'NH': 'New Hampshire',
    'NJ': 'New Jersey',
    'NM': 'New Mexico',
    'NV': 'Nevada',
    'NY': 'New York',
    'OH': 'Ohio',
    'OK': 'Oklahoma',
    'OR': 'Oregon',
    'PA': 'Pennsylvania',
    'RI': 'Rhode Island',
    'SC': 'South Carolina',
    'SD': 'South Dakota',
    'TN': 'Tennessee',
    'TX': 'Texas',
    'UT': 'Utah',
    'VA': 'Virginia',
    'VT': 'Vermont',
    'WA': 'Washington',
    'WI': 'Wisconsin',
    'WV': 'West Virginia',
    'WY': 'Wyoming'
}

# Si crea una nuova tabella con i codici degli stati, 
#i nomi e la popolazione

pop.columns = ["col1","col2"]
pop["codes"] = states.keys()
pop["states"] = states.values()
pop["population"] = pop.iloc[:,1]
pop.drop(columns = ["col1","col2"], inplace = True)
for i in range(len(dogtravel["contact_state"])):
    if dogtravel["contact_state"][i] == "17325":
        dogtravel["contact_state"][i] = "PA"
    else:
        pass
pop


# Nuova tabella in cui per ogni stato si contano il numero di viaggi

q9t1 = dogtravel.groupby(["contact_state"]).count()["index"]
q9t1 = pd.DataFrame(q9t1)
q9t1.reset_index(level=0, inplace=True)
q9t1.columns = ["codes","travels"]

# Join sugli stati tra la tabella creata in precedenza e quest'ultima,
#aggiungendo una colonna con il rapporto tra viaggi e popolazione

q9r1 = pd.merge(pop,q9t1,left_on="codes",right_on="codes",how="left")
for i in range(len(q9r1["population"])):
    q9r1["population"][i] = int(q9r1["population"][i].replace(".",""))
q9r1["ratio"] = q9r1["travels"] / q9r1["population"]
q9r1["ratio"] = q9r1["ratio"].replace(np.nan, 0).round(6)
q9r1["travels"] = q9r1["travels"].replace(np.nan, 0).astype(int)
q9r1


# 10) For each dog, compute the number of days from the posted day to 
#the day of last access.

# Prima si trasformano le variabili posted e accessed in datetime

dogs["posted"] = pd.to_datetime(dogs["posted"])
dogs["posted"] = dogs["posted"].dt.date
dogs["accessed"] = pd.to_datetime(dogs["accessed"])
dogs["accessed"] = dogs["accessed"].dt.date

# Calcolo dei giorni passati da "accessed" a "posted"

passed_days = dogs["accessed"] - dogs["posted"]
dogs1 = dogs
dogs1["passed_days"] = passed_days
result10 = dogs1[["id","passed_days"]]
HTML(result10.to_html(index=False))


# 11) Partition the dogs according to the number of weeks from the posted 
#day to the day of last access.

# Prima si trasformano i giorni della tabella precedente in settimane 

table11 = result10
table11['weeks'] = table11['passed_days']/np.timedelta64(1,'W')

# Si arrotonda per difetto, quindi meno di 7 giorni vengono considerati 
#come 0 settimane  

table11['weeks'] = (table11['weeks']).apply(np.floor)
table11['weeks'] = table11['weeks'].astype(int)

# Si raggruppano i cani in base al numero di settimane 

result11 = table11[["id","weeks"]]
result11 = result11.groupby(["weeks","id"]).count()
result11


# 12) Find for duplicates in the dogs dataset.

#group by similar dogs according to sex and primary and secondary breed
similardogs = dogs.groupby(["sex","breed_primary","breed_secondary"]).count() 

#select the dogs with same breeds and sex that appear more than once
similardogs = pd.DataFrame(similardogs[similardogs['id']>1]['id']) 

#reset indices
similardogs.reset_index(inplace = True)  
similardogs


df_sec_breed = []

#tramite un ciclo for racchiudi in una lista tutti i dataframe contenenti
# cani con stesso sesso, primary e secondary breed.

for x in range(similardogs.shape[0]): 
  df = dogs.loc[(dogs['sex']==similardogs.loc[x]['sex']) & (dogs['breed_primary']==similardogs.loc[x]['breed_primary'])&(dogs['breed_secondary']==similardogs.loc[x]['breed_secondary'])]
  df_sec_breed.append(df[["id","description"]])
    
from difflib import SequenceMatcher


# funzione per la similarità: ratio restituisce un punteggio di similarità
#tra 0 e 1 tra due stringhe.

# ratio è calcolato come: 2M/T; dove T è il numero totale di elementi ed M 
#è il numero totale di match.

def similar(a, b):
  if pd.isnull(a) or pd.isnull(b):
    return 0
  else:
    return SequenceMatcher(None, a, b).ratio()

from itertools import combinations



#per ogni dataframe contenuto in df_sec_breed viene calcolata la 
# somiglianza per ogni coppia possibile in quel dataframe.

for x in df_sec_breed:
  for index in list(combinations(x.index,2)):
    paired = pd.DataFrame(x.loc[index,:])
    if similar(paired.iloc[0]['description'], paired.iloc[1]['description']) >= 0.90:
      print(f"dog {paired.iloc[0]['id']} and dog {paired.iloc[1]['id']} are duplicates. Similarity is {similar(paired.iloc[0]['description'], paired.iloc[1]['description'])}")
    else:
      pass
print(x)

	id	org_id	url	type.x	species	breed_primary	breed_secondary	breed_mixed	breed_unknown	color_primary	...	contact_city	contact_state	contact_zip	contact_country	stateQ	accessed	type.y	description	stay_duration	stay_cost
644	41330726	NV173	https://www.petfinder.com/dog/gunther-gunny-41...	Dog	Dog	German Shepherd Dog	NaN	False	False	NaN	...	Las Vegas	NV	89146	US	89009	2019-09-20	NaN	Meet handsome 3 year old Gunther. Gunther came...	108	256.88
5549	38169117	AZ414	https://www.petfinder.com/dog/annabelle-annie-...	Dog	Dog	Boxer	Pit Bull Terrier	True	False	Black	...	Chandler	AZ	85249	US	AZ	2019-09-20	NaN	You can fill out an adoption application onlin...	80	130.77
10888	45833989	NY98	https://www.petfinder.com/dog/pepper-courtesy-...	Dog	Dog	Beagle	NaN	False	False	NaN	...	Albany	NY	12220	US	CT	2019-09-20	NaN	This is Pepper. He is a 15 year old tri-color ...	86	180.70
11983	45515547	NY98	https://www.petfinder.com/dog/cooper-courtesy-...	Dog	Dog	Mixed Breed	NaN	False	False	NaN	...	Albany	NY	12220	US	CT	2019-09-20	NaN	Cooper is 13 years old, but according to a ver...	105	400.82
12495	45294115	NY98	https://www.petfinder.com/dog/daisy-courtesy-l...	Dog	Dog	Basset Hound	NaN	False	False	Brown / Chocolate	...	Albany	NY	12220	US	CT	2019-09-20	NaN	â¢Basset Hound, female, â¢10 years \n\nDelig...	57	82.61
12600	45229004	NY1436	https://www.petfinder.com/dog/elmo-momo-452290...	Dog	Dog	American Bulldog	NaN	True	False	NaN	...	Saugerties	NY	12477	US	CT	2019-09-20	NaN	Hello i'm MoMo or Elmo , 7 year old, mixed bre...	73	136.30
12613	45227052	NY1436	https://www.petfinder.com/dog/bianca-pinky-452...	Dog	Dog	Mixed Breed	NaN	False	False	White / Cream	...	Saugerties	NY	12477	US	CT	2019-09-20	NaN	Hello I'm Bianca, a female, 7 year old mixed b...	107	231.31
17619	45569380	CA1209	https://www.petfinder.com/dog/baby-girl-455693...	Dog	Dog	Maltese	NaN	False	False	White / Cream	...	Bristow	VA	20136	US	DC	2019-09-20	NaN	This 10-year young senior is very sweet and lo...	76	263.63
18611	44694387	MD295	https://www.petfinder.com/dog/king-bert-bertie...	Dog	Dog	Fox Terrier	Chihuahua	True	False	Bicolor	...	Silver Spring	MD	20905	US	DC	2019-09-20	NaN	\"Bertie\" came to us from the shelter. He wa...	61	158.84
19747	36978896	VA127	https://www.petfinder.com/dog/maddie-cutie-pat...	Dog	Dog	Alaskan Malamute	NaN	False	False	Bicolor	...	Gettysburg	PA	17325	US	DC	2019-09-20	NaN	Maddie is our little Miss Cutie Patootie! She ...	119	431.66
19845	33218331	VA127	https://www.petfinder.com/dog/bucky-are-u-my-h...	Dog	Dog	Alaskan Malamute	Siberian Husky	True	False	Gray / Blue / Silver	...	Gettysburg	PA	17325	US	DC	2019-09-20	NaN	Born in August 2014, Bucky has a great sense o...	98	346.67
22161	42092005	PA738	https://www.petfinder.com/dog/chloe-little-ras...	Dog	Dog	Pit Bull Terrier	Pointer	True	False	Black	...	Media	PA	19063	US	DE	2019-09-20	NaN	Meet Chloe our \"Little Rascal.\	113	279.59
22229	39594038	PA562	https://www.petfinder.com/dog/cozi-h-wilson-lo...	Dog	Dog	Pit Bull Terrier	NaN	True	False	NaN	...	Feasterville	PA	19053	US	DE	2019-09-20	NaN	01.04.19\nHi Folks! Iâm Cozi and Iâm so gl...	88	360.84
29283	45895274	IL688	https://www.petfinder.com/dog/gibson-edgar-gib...	Dog	Dog	Labrador Retriever	NaN	True	False	Brown / Chocolate	...	Paris	IL	61944	US	IL	2019-09-20	NaN	Gibson Edgar is a 12 month old male Lab mix th...	74	121.56
30471	45964719	IN694	https://www.petfinder.com/dog/puppies-berries-...	Dog	Dog	American Staffordshire Terrier	Border Collie	True	False	Black	...	Mooresville	IN	46158	US	IN	2019-09-20	NaN	11 Berry Pups\nIndividual pics will be done. ...	127	429.87
31581	44538917	OH907	https://www.petfinder.com/dog/leila-courtesy-p...	Dog	Dog	Australian Shepherd	Husky	True	False	Tricolor (Brown, Black, & White)	...	Ross	OH	45061	US	IN	2019-09-20	NaN	THIS IS A COURTESY POST (this means that any a...	92	188.49
31888	41430442	OH907	https://www.petfinder.com/dog/eden-in-training...	Dog	Dog	Hound	NaN	True	False	White / Cream	...	Ross	OH	45061	US	IN	2019-09-20	NaN	Eden is estimated 3 years old and weighs 40 lb...	97	238.60
33000	45907639	IN714	https://www.petfinder.com/dog/coach-chris-mack...	Dog	Dog	Chihuahua	Beagle	True	False	Yellow / Tan / Blond / Fawn	...	Jeffersonville	IN	47131	US	KY	2019-09-20	NaN	Mack is a 2 year old, 13 pound furry chihuahua...	101	478.36
33527	45362806	OH852	https://www.petfinder.com/dog/liberty-libby-45...	Dog	Dog	Pomeranian	NaN	False	False	NaN	...	Cincinnati	OH	45249	US	KY	2019-09-20	NaN	Liberty came to us on the 3rd of July - she ha...	65	185.05
34188	32590894	IN595	https://www.petfinder.com/dog/samson-sam-32590...	Dog	Dog	Mastiff	Labrador Retriever	True	False	Black	...	Paoli	IN	47454	US	KY	2019-09-20	NaN	DUE TO PROBLEMS WITH THE CURRENT PETFINDER UPD...	116	413.96
35065	31426754	LA256	https://www.petfinder.com/dog/sully-sullivan-3...	Dog	Dog	Pit Bull Terrier	NaN	False	False	Black	...	lake charles	LA	70601	US	LA	2019-09-20	NaN	Hi my name is Sully! I get along with most dog...	122	204.63
44830	46037827	NM133	https://www.petfinder.com/dog/periwinkle-wink-...	Dog	Dog	Australian Shepherd	Spaniel	True	False	Brown / Chocolate	...	Albuquerque	NM	87108	US	NM	2019-09-20	NaN	We take the time to post pertinent information...	85	296.75
53168	44044071	TN669	https://www.petfinder.com/dog/raymond-reddingt...	Dog	Dog	American Staffordshire Terrier	NaN	True	False	Red / Chestnut / Orange	...	Cookeville	TN	38506	US	TN	2019-09-20	NaN	Red is a 4-5 yr old Staffey mix that is a wig...	88	230.12
53539	27521132	TN577	https://www.petfinder.com/dog/norma-rose-rosie...	Dog	Dog	Yellow Labrador Retriever	American Staffordshire Terrier	True	False	Yellow / Tan / Blond / Fawn	...	Nashville	TN	37189	US	TN	2019-09-20	NaN	Robyn's Nest Animal Rescue & SanctuaryNashvill...	93	300.50
55434	38473806	VA486	https://www.petfinder.com/dog/markey-moose-384...	Dog	Dog	Hound	NaN	False	False	White / Cream	...	Rustburg	VA	24588	US	VA	2019-09-20	NaN	Markey \"Moose\" first came to us after being ...	78	71.15
55467	34101432	VA628	https://www.petfinder.com/dog/george-boy-georg...	Dog	Dog	Labrador Retriever	NaN	True	False	Yellow / Tan / Blond / Fawn	...	Midlothian	VA	23112	US	VA	2019-09-20	NaN	Contact dojennings@Comcast.net or Debbie Jenni...	115	320.23
55915	45958435	WA581	https://www.petfinder.com/dog/skipper-45958435...	Dog	Dog	Yorkshire Terrier	Terrier	True	False	Tricolor (Brown, Black, & White)	...	Seattle	WA	98106	US	WA	2019-09-20	NaN	For a quicker response please submit a online ...	102	338.18
55975	45927580	WA581	https://www.petfinder.com/dog/bella-45927580/w...	Dog	Dog	Chihuahua	NaN	False	False	White / Cream	...	Seattle	WA	98106	US	WA	2019-09-20	NaN	Meet \"Bella\" She is a adorable and 7 pounds ...	102	407.51
56013	45916348	WA581	https://www.petfinder.com/dog/cody-45916348/wa...	Dog	Dog	Golden Retriever	Terrier	True	False	Bicolor	...	Seattle	WA	98106	US	WA	2019-09-20	NaN	*Please apply online at www.6dogrees.com to ge...	92	164.55
56248	45733027	WA581	https://www.petfinder.com/dog/gracie-45733027/...	Dog	Dog	Papillon	Cavalier King Charles Spaniel	True	False	Golden	...	Seattle	WA	98106	US	WA	2019-09-20	NaN	Meet \"Gracie\" a very beautiful 11 pound,fema...	82	168.54
56464	45413997	WA581	https://www.petfinder.com/dog/jameson-45413997...	Dog	Dog	Rat Terrier	Chihuahua	True	False	Tricolor (Brown, Black, & White)	...	Seattle	WA	98106	US	WA	2019-09-20	NaN	Meet \"Jameson\" He is a very handsome 9 pound...	108	297.27
56473	45406516	WA581	https://www.petfinder.com/dog/canelo-45406516/...	Dog	Dog	Chihuahua	Terrier	True	False	Bicolor	...	Seattle	WA	98106	US	WA	2019-09-20	NaN	Meet Canelo a adorable small, tan very sweet...	94	312.10
56541	45264615	WA581	https://www.petfinder.com/dog/noni-45264615/wa...	Dog	Dog	Chihuahua	Terrier	True	False	Black	...	Seattle	WA	98106	US	WA	2019-09-20	NaN	Meet \"Noni\" She is a Black and white smooth ...	77	290.16

	Earliest	Latest
breed_primary
Affenpinscher	2012-03-08 10:27:33+00:00	2019-09-14 10:10:51+00:00
Afghan Hound	2017-06-29 23:28:51+00:00	2019-07-27 00:38:48+00:00
Airedale Terrier	2014-06-13 12:59:36+00:00	2019-09-19 18:40:39+00:00
Akbash	2019-07-21 00:35:59+00:00	2019-08-23 17:11:04+00:00
Akita	2012-03-03 09:31:08+00:00	2019-09-20 15:19:57+00:00
...	...	...
Wirehaired Pointing Griffon	2016-06-29 20:03:55+00:00	2016-06-29 20:03:55+00:00
Wirehaired Terrier	2012-11-27 14:07:54+00:00	2019-09-19 22:52:45+00:00
Xoloitzcuintli / Mexican Hairless	2007-02-01 00:00:00+00:00	2019-09-08 11:15:54+00:00
Yellow Labrador Retriever	2010-05-31 00:00:00+00:00	2019-09-20 06:30:27+00:00
Yorkshire Terrier	2012-02-04 17:14:52+00:00	2019-09-20 16:56:38+00:00

		stay_duration	stay_cost
age	size
Adult	Extra Large	89.015414	232.591561
	Large	89.531943	238.661141
	Medium	89.421036	238.258977
	Small	89.407479	238.974838
Baby	Extra Large	87.032967	237.180879
	Large	89.701564	238.698827
	Medium	89.577668	237.108131
	Small	89.958291	239.083810
Senior	Extra Large	88.861111	235.232361
	Large	88.984298	237.507364
	Medium	89.810052	238.514615
	Small	89.073626	238.282286
Young	Extra Large	90.586345	245.835582
	Large	90.104206	238.149506
	Medium	89.515123	239.304603
	Small	89.814275	241.540069

	id	Travels	breed_primary
0	16657005	4	Pit Bull Terrier
1	20905974	5	Chow Chow
2	24894870	4	Hound
3	24894894	4	Hound
4	33218331	7	Alaskan Malamute
...	...	...	...
558	46042569	3	Labrador Retriever
559	46042587	3	Labrador Retriever
560	46042618	3	Labrador Retriever
561	46043099	3	Labrador Retriever
562	46043149	3	Labrador Retriever

	codes	states	population	travels	ratio
0	AK	Alaska	5024279	0	0.000000
1	AL	Alabama	733391	75	0.000102
2	AR	Arkansas	7151502	10	0.000001
3	AZ	Arizona	3011524	70	0.000023
4	CA	California	39538223	28	0.000001
5	CO	Colorado	5773714	103	0.000018
6	CT	Connecticut	3605944	90	0.000025
7	DC	District of Columbia	989948	112	0.000113
8	DE	Delaware	689545	57	0.000083
9	FL	Florida	21538187	133	0.000006
10	GA	Georgia	10711908	109	0.000010
11	HI	Hawaii	1455271	0	0.000000
12	IA	Iowa	1839106	22	0.000012
13	ID	Idaho	12812508	0	0.000000
14	IL	Illinois	6785528	37	0.000005
15	IN	Indiana	3190369	65	0.000020
16	KS	Kansas	2937880	3	0.000001
17	KY	Kentucky	4505836	31	0.000007
18	LA	Louisiana	4657757	5	0.000001
19	MA	Massachusetts	1362359	107	0.000079
20	MD	Maryland	6177224	379	0.000061
21	ME	Maine	7029917	361	0.000051
22	MI	Michigan	10077331	67	0.000007
23	MN	Minnesota	5706494	190	0.000033
24	MO	Missouri	2961279	42	0.000014
25	MS	Mississippi	6154913	6	0.000001
26	MT	Montana	1084225	0	0.000000
27	NC	North Carolina	1961504	187	0.000095
28	ND	North Dakota	3104614	0	0.000000
29	NE	Nebraska	1377529	0	0.000000
30	NH	New Hampshire	9288994	44	0.000005
31	NJ	New Jersey	2117522	552	0.000261
32	NM	New Mexico	20201249	82	0.000004
33	NV	Nevada	10439388	20	0.000002
34	NY	New York	779094	490	0.000629
35	OH	Ohio	11799448	177	0.000015
36	OK	Oklahoma	3959353	31	0.000008
37	OR	Oregon	4237256	32	0.000008
38	PA	Pennsylvania	13002700	326	0.000025
39	RI	Rhode Island	1097379	130	0.000118
40	SC	South Carolina	5118425	21	0.000004
41	SD	South Dakota	886667	0	0.000000
42	TN	Tennessee	6910840	112	0.000016
43	TX	Texas	29145505	2	0.000000
44	UT	Utah	3271616	66	0.000020
45	VA	Virginia	643077	1025	0.001594
46	VT	Vermont	8631393	49	0.000006
47	WA	Washington	7705281	634	0.000082
48	WI	Wisconsin	1793716	83	0.000046
49	WV	West Virginia	5893718	27	0.000005
50	WY	Wyoming	576851	0	0.000000

	index	id	contact_city	contact_state	description	found	manual	remove	still_there
0	0	44520267	Anoka	MN	Boris is a handsome mini schnauzer who made hi...	Arkansas	NaN	NaN	NaN
1	1	44698509	Groveland	FL	Duke is an almost 2 year old Potcake from Abac...	Bahamas	Bahamas	NaN	NaN
2	2	45983838	Adamstown	MD	Zac Woof-ron is a heartthrob movie star lookin...	Maryland	Maryland	NaN	NaN
3	3	44475904	Saint Cloud	MN	~~Came in to the shelter as a transfer from an...	Adaptil	NaN	True	NaN
4	4	43877389	Pueblo	CO	Palang is such a sweetheart. She loves her peo...	Afghanistan	NaN	NaN	NaN
...	...	...	...	...	...	...	...	...	...
6189	6189	40492179	Fairmont	WV	Please contact Pet (information@pethelpersinc....	WV	NaN	True	NaN
6190	6190	45799729	Eagle Mountain	UT	Shiny is an approximately 4-6-year-old spayed ...	Wyoming	NaN	NaN	NaN
6191	6191	34276515	Newnan	GA	Yanni is a Male Great Pyrenees that we rescue...	Yazmin	NaN	True	NaN
6192	6192	44519341	Dayton	OH	Callie is a 14 year old Chihuahua whose owner ...	Ohio	Ohio	NaN	NaN
6193	6193	36659999	New York	NY	This guy loves to be outside so a person w/ a ...	Zazu	NaN	True	NaN

	codes	states	population
0	AK	Alaska	5.024.279
1	AL	Alabama	733.391
2	AR	Arkansas	7.151.502
3	AZ	Arizona	3.011.524
4	CA	California	39.538.223
5	CO	Colorado	5.773.714
6	CT	Connecticut	3.605.944
7	DC	District of Columbia	989.948
8	DE	Delaware	689.545
9	FL	Florida	21.538.187
10	GA	Georgia	10.711.908
11	HI	Hawaii	1.455.271
12	IA	Iowa	1.839.106
13	ID	Idaho	12.812.508
14	IL	Illinois	6.785.528
15	IN	Indiana	3.190.369
16	KS	Kansas	2.937.880
17	KY	Kentucky	4.505.836
18	LA	Louisiana	4.657.757
19	MA	Massachusetts	1.362.359
20	MD	Maryland	6.177.224
21	ME	Maine	7.029.917
22	MI	Michigan	10.077.331
23	MN	Minnesota	5.706.494
24	MO	Missouri	2.961.279
25	MS	Mississippi	6.154.913
26	MT	Montana	1.084.225
27	NC	North Carolina	1.961.504
28	ND	North Dakota	3.104.614
29	NE	Nebraska	1.377.529
30	NH	New Hampshire	9.288.994
31	NJ	New Jersey	2.117.522
32	NM	New Mexico	20.201.249
33	NV	Nevada	10.439.388
34	NY	New York	779.094
35	OH	Ohio	11.799.448
36	OK	Oklahoma	3.959.353
37	OR	Oregon	4.237.256
38	PA	Pennsylvania	13.002.700
39	RI	Rhode Island	1.097.379
40	SC	South Carolina	5.118.425
41	SD	South Dakota	886.667
42	TN	Tennessee	6.910.840
43	TX	Texas	29.145.505
44	UT	Utah	3.271.616
45	VA	Virginia	643.077
46	VT	Vermont	8.631.393
47	WA	Washington	7.705.281
48	WI	Wisconsin	1.793.716
49	WV	West Virginia	5.893.718
50	WY	Wyoming	576.851


weeks	id
0	39878821
	45192040
	45703940
	45892869
	45938106
...	...
729	5142790
746	4527948
811	2613506
812	2592031
852	604115

	sex	breed_primary	breed_secondary	id
0	Female	Akita	German Shepherd Dog	2
1	Female	Akita	Hound	2
2	Female	Akita	Labrador Retriever	3
3	Female	Akita	Mixed Breed	2
4	Female	Akita	Pit Bull Terrier	3
...	...	...	...	...
2046	Male	Yorkshire Terrier	Poodle	10
2047	Male	Yorkshire Terrier	Schnauzer	6
2048	Male	Yorkshire Terrier	Shih Tzu	8
2049	Male	Yorkshire Terrier	Terrier	6
2050	Male	Yorkshire Terrier	Wirehaired Terrier	3

	imbalance
contact_state
AK	1
AL	-4
AR	-7
AZ	114
CA	110
CO	-51
CT	58
DC	-16
DE	0
FL	101
GA	25
HI	13
IA	81
ID	1
IL	101
IN	178
KS	0
KY	109
LA	23
MA	28
MD	136
ME	-29
MI	39
MN	34
MO	70
MS	-40
MT	4
NB	0
NC	63
ND	8
NE	-2
NH	-9
NJ	56
NM	17
NV	54
NY	117
OH	205
OK	8
OR	25

id	passed_days
46042150	0 days
46042002	0 days
46040898	0 days
46039877	0 days
46039306	0 days
46039304	0 days
46039303	0 days
46039302	0 days
46039301	0 days
46038709	0 days
46038708	0 days
46038703	0 days
46038700	0 days
46038243	0 days
46038070	0 days
46038064	0 days
46038065	0 days
46038067	0 days
46038068	0 days
46038060	0 days
46038062	0 days
46038063	0 days
46038061	0 days
46037951	0 days
46037918	0 days
46037881	0 days
46037860	0 days
46037820	0 days
46037762	0 days
46037742	0 days
46037637	0 days
46037534	0 days
46036459	1 days
46035351	1 days
46035350	1 days
46035353	1 days
46035346	1 days
46035344	1 days
46035342	1 days
46034532	1 days